import eu.kanade.tachiyomi.source.model.UpdateStrategy;
import kotlin.collections.List;
import kotlin.Boolean;
import kotlin.String;

CREATE TABLE mangas(
    _id INTEGER NOT NULL PRIMARY KEY,
    source INTEGER NOT NULL,
    url TEXT NOT NULL,
    artist TEXT,
    author TEXT,
    description TEXT,
    genre TEXT AS List<String>,
    title TEXT NOT NULL,
    status INTEGER NOT NULL,
    thumbnail_url TEXT,
    favorite INTEGER AS Boolean NOT NULL,
    last_update INTEGER,
    next_update INTEGER,
    initialized INTEGER AS Boolean NOT NULL,
    viewer INTEGER NOT NULL,
    chapter_flags INTEGER NOT NULL,
    cover_last_modified INTEGER NOT NULL,
    date_added INTEGER NOT NULL,
    update_strategy INTEGER AS UpdateStrategy NOT NULL DEFAULT 0,
    calculate_interval INTEGER DEFAULT 0 NOT NULL,
    last_modified_at INTEGER NOT NULL DEFAULT 0,
    favorite_modified_at INTEGER
);

CREATE INDEX library_favorite_index ON mangas(favorite) WHERE favorite = 1;
CREATE INDEX mangas_url_index ON mangas(url);

CREATE TRIGGER update_last_favorited_at_mangas
AFTER UPDATE OF favorite ON mangas
BEGIN
  UPDATE mangas
  SET favorite_modified_at = strftime('%s', 'now')
  WHERE _id = new._id;
END;

CREATE TRIGGER update_last_modified_at_mangas
AFTER UPDATE ON mangas
FOR EACH ROW
BEGIN
  UPDATE mangas
  SET last_modified_at = strftime('%s', 'now')
  WHERE _id = new._id;
END;

getMangaById:
SELECT *
FROM mangas
WHERE _id = :id;

-- TODO: this should ideally never really have more than 1 result
getMangaByUrlAndSource:
SELECT *
FROM mangas
WHERE url = :url
AND source = :source
LIMIT 1;

getFavorites:
SELECT *
FROM mangas
WHERE favorite = 1;

getAllManga:
SELECT *
FROM mangas;

getAllMangaSourceAndUrl:
SELECT source, url
FROM mangas;

getMangasWithFavoriteTimestamp:
SELECT *
FROM mangas
WHERE favorite_modified_at IS NOT NULL;

getSourceIdWithFavoriteCount:
SELECT
source,
count(*)
FROM mangas
WHERE favorite = 1
GROUP BY source;

getFavoriteBySourceId:
SELECT *
FROM mangas
WHERE favorite = 1
AND source = :sourceId;

getDuplicateLibraryManga:
SELECT *
FROM mangas
WHERE favorite = 1
AND LOWER(title) = :title
AND _id != :id;

resetViewerFlags:
UPDATE mangas
SET viewer = 0;

getSourceIdsWithNonLibraryManga:
SELECT source, COUNT(*) AS manga_count
FROM mangas
WHERE favorite = 0
GROUP BY source;

deleteMangasNotInLibraryBySourceIds:
DELETE FROM mangas
WHERE favorite = 0
AND source IN :sourceIds;

insert:
INSERT INTO mangas(source, url, artist, author, description, genre, title, status, thumbnail_url, favorite, last_update, next_update, initialized, viewer, chapter_flags, cover_last_modified, date_added, update_strategy, calculate_interval, last_modified_at)
VALUES (:source, :url, :artist, :author, :description, :genre, :title, :status, :thumbnailUrl, :favorite, :lastUpdate, :nextUpdate, :initialized, :viewerFlags, :chapterFlags, :coverLastModified, :dateAdded, :updateStrategy, :calculateInterval, strftime('%s', 'now'));

update:
UPDATE mangas SET
    source = coalesce(:source, source),
    url = coalesce(:url, url),
    artist = coalesce(:artist, artist),
    author = coalesce(:author, author),
    description = coalesce(:description, description),
    genre = coalesce(:genre, genre),
    title = coalesce(:title, title),
    status = coalesce(:status, status),
    thumbnail_url = coalesce(:thumbnailUrl, thumbnail_url),
    favorite = coalesce(:favorite, favorite),
    last_update = coalesce(:lastUpdate, last_update),
    next_update = coalesce(:nextUpdate, next_update),
    initialized = coalesce(:initialized, initialized),
    viewer = coalesce(:viewer, viewer),
    chapter_flags = coalesce(:chapterFlags, chapter_flags),
    cover_last_modified = coalesce(:coverLastModified, cover_last_modified),
    date_added = coalesce(:dateAdded, date_added),
    update_strategy = coalesce(:updateStrategy, update_strategy),
    calculate_interval = coalesce(:calculateInterval, calculate_interval)
WHERE _id = :mangaId;

selectLastInsertedRowId:
SELECT last_insert_rowid();
